package com.roc.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;

import com.roc.bean.MenuBean;
import com.roc.dao.DaoException;
import com.roc.dao.operator.IPowerInfoDao;
import com.roc.pojo.MerchantModuleRight;
import com.roc.pojo.Operators;
import com.roc.util.constant.Constant;
import com.roc.util.constant.Role;

/**
 * 
 * @author 陈锴
 * 
 * 操作员权限操作
 *
 */

@Repository
public class PowerInfoDaoImpl extends BaseDaoImpl implements IPowerInfoDao {

	@Override
	//查询操作员权限
	public List<MerchantModuleRight> queryOperatorPowerInfoDao(String accountNo)
			throws DaoException {
		List<MerchantModuleRight> moduleList=new ArrayList<MerchantModuleRight>();
		try {
			Session session = sessionFactory.getCurrentSession();
			String hql="from MerchantModuleRight m where merchantNo = '"+accountNo+"' ";
			Query query=session.createQuery(hql);
			moduleList=query.list();
		} catch (HibernateException e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询操作员权限出错!");
		}
		return moduleList;
	}

	@Override
	public List<MenuBean> queryModuleCategoryList(Operators logingOperators,String type)
			throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<MenuBean> menuBeanList=new ArrayList<MenuBean>();
		
		try {//and m.roleType like '%"+logingOperators.getRoleType()+"%'
			String sql="";
			if(Role.AGENT.getCode().equals(logingOperators.getRoleType())){
				sql=" select c.moduleCategoryCode,c.moduleCategoryName,m.moduleCode,m.moduleName"
						+ " from ModuleCategory c,Module m,MerchantModuleRight mm "
						+ " where c.moduleCategoryCode = m.categoryCode and mm.moduleCode=m.moduleCode and mm.merchantNo='"+logingOperators.getAccountNo()+"' "
						+ " and m.roleType like '%"+logingOperators.getRoleType()+"%' "
						+ " ORDER BY c.moduleCategoryCode DESC ";
				
			}else{
				 if(null == type){
					 sql="select c.moduleCategoryCode,c.moduleCategoryName,m.moduleCode,m.moduleName "
								+" from ModuleCategory c,Module m,MerchantModuleRight mm  "
								+" where c.moduleCategoryCode = m.categoryCode and mm.moduleCode=m.moduleCode and mm.merchantNo='"+logingOperators.getAccountNo()+"' "
								+" ORDER BY c.moduleCategoryCode DESC ";
				 }else{
					 //数据分析平台查询操作员为“admin”
					 sql="select c.moduleCategoryCode,c.moduleCategoryName,m.moduleCode,m.moduleName "
								+" from ModuleCategory c,Module m  "
								+" where c.moduleCategoryCode = m.categoryCode  "
								+" and m.operators='admin' "
								+" ORDER BY c.moduleCategoryCode DESC ";
				 }
				
			}
			
			Query query=session.createQuery(sql);
			Iterator iterator=query.list().iterator();
			while (iterator.hasNext()) {
				MenuBean bean=new MenuBean();
				Object[] object=(Object[])iterator.next();
				bean.setCategoryCode((String)object[0]);
				bean.setCategoryName((String)object[1]);
				bean.setModuleCode((String)object[2]);
				bean.setModuleName((String)object[3]);
				menuBeanList.add(bean);
			}
		} catch (HibernateException e) {
			logger.error(e.getMessage(),e);
			throw new DaoException("查询目录信息和模块功能信息失败！");
		}
		
		return menuBeanList;
	}

	@Override
	public List<MerchantModuleRight> queryModuleTellerByNo(String accountNo,String type)
			throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<MerchantModuleRight> moduleRightList=new ArrayList<MerchantModuleRight>();
		
		try {
			String hql="";
			if(null == type){
				hql="from MerchantModuleRight r where r.merchantNo = :merchantNo";
			}else{
				 hql="from MerchantModuleRight r where r.operators='admin' and  r.merchantNo = :merchantNo  "; //数据分析的操作员均为admin 
			}
			
			Query query=session.createQuery(hql);
			query.setString("merchantNo", accountNo);
			moduleRightList=query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询操作员权限失败！");
		}
		
		return moduleRightList;
	}

	@Override
	public void removeModuleOperator(String accountNo,String type) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		
		try {
			String hql ="";
			if(null == type){
				//收单管理平台的相关权限
				hql="delete from MerchantModuleRight r where r.operators<>'admin' and r.merchantNo = :merchantNo";
			}else {
				//数据分析平台的相关权限
				hql="delete from MerchantModuleRight r where r.operators='admin' and r.merchantNo = :merchantNo";
			}
			session.createQuery(hql).setString("merchantNo", accountNo).executeUpdate();
		} catch (HibernateException e) {
			throw new DaoException("删除操作员权限失败！");
		}
		
	}

	@Override
	public
	boolean updateByAccountNo(String AccountNo)throws DaoException{
		// TODO Auto-generated method stub
		Session session=sessionFactory.getCurrentSession();
		
		try{
			session.createSQLQuery("update t_Operators operators set operators.status='00' where operators.account_no='"+AccountNo+"'").addEntity("operators",Operators.class).executeUpdate();
			//session.createSQLQuery("insert into t_Auditing_item values");
			
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("修改对象失败" + AccountNo, e);
		}

		return false;
	}

	@Override
	public
	Operators queryByAccountNo(String AccountNo) throws DaoException{
		// TODO Auto-generated method stub
	Session session=sessionFactory.getCurrentSession();
		//Operators operator;
	    Operators oper=new Operators(); 
		//List list;
		StringBuffer sb=new StringBuffer();
		sb.append(" select o.account_no as \"accountNo\",o.real_name as \"realName\",CASE WHEN o.sex = 1 THEN '男' ELSE '女' END as \"sex\",o.mobile as \"mobile\",r.org_name as \"orgName\",l.role_name as \"roleName\",o.card_no as \"cardNo\",CASE  WHEN o.status = '01' THEN '待审核' END as \"status\", o.card_type as \"cardType\",o.telphone as \"telphone\",o.bank_deposit as \"bankDeposit\",o.bank_acct_no as \"bankAcctNo\",o.bank_acct_name as \"bankAcctName\",o.email as \"email\",o.address as\"address\",o.remark as\"remark\"");
		sb.append(" from t_operators o ");
		sb.append(" INNER JOIN t_org r ");
		sb.append(" ON o.org_code = r.org_code ");
		sb.append(" INNER JOIN t_role l ");
		sb.append(" ON o.role_type = l.role_code ");
		sb.append(" WHERE o.account_no='"+AccountNo+"'");
		 
	    try{
	    	 SQLQuery query=session.createSQLQuery(sb.toString());
	    	 query.setResultTransformer(Transformers.aliasToBean(Operators.class));
	    	 query.addScalar("accountNo", StringType.INSTANCE);
	    	 query.addScalar("realName", StringType.INSTANCE);
	    	 query.addScalar("sex", StringType.INSTANCE);
	    	 query.addScalar("mobile", StringType.INSTANCE);
	    	 query.addScalar("orgName", StringType.INSTANCE);
	    	 query.addScalar("roleName", StringType.INSTANCE);
	    	 query.addScalar("cardType", StringType.INSTANCE);
	    	 query.addScalar("cardNo", StringType.INSTANCE);
	    	 query.addScalar("telphone", StringType.INSTANCE);
	    	 query.addScalar("bankDeposit", StringType.INSTANCE);
	    	 query.addScalar("bankAcctNo", StringType.INSTANCE);
	    	 query.addScalar("bankAcctName", StringType.INSTANCE);
	    	 query.addScalar("email", StringType.INSTANCE);
	    	 query.addScalar("address", StringType.INSTANCE);
	    	 query.addScalar("remark", StringType.INSTANCE);
	    	// query.addScalar("id", StringType.INSTANCE);
	        
	    	 oper=(Operators) query.uniqueResult();
	    
	    }catch(Exception e){
	    	logger.error(e.getMessage(),e);
	    	throw new DaoException("Dao查询失败"+AccountNo,e);
	    }
		
		
		return oper;
	}

	@Override
	public List<Operators> queryRealNameInfoByName(String realName , String orgCode, String roleType, int flag)
			throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<Operators> operatorsList=new ArrayList<Operators>();
		
		try {
			String hql="from Operators o where  o.orgCode = :orgCode and  o.realName like :realName ";
			// 如果flag为1 ，查询收单事业部下的代理商
		/*	if( flag == 1 ) {
				hql = hql + " and o.companyid in ( select t1.id from Operators t1 where t1.superiorId = '" + Constant.SHOUDANSHIYEBU_ID + "' and t1.roleType = '03' ) " ;
			}*/
			if( null != roleType && !"".equals(roleType) ) {
				hql = hql + " and o.roleType in (" + roleType + ") ";
			}
			
			Query query=session.createQuery(hql);
			query.setString("orgCode", orgCode);
			query.setString("realName", realName);
		//	query.setString("roleType", roleType);
			operatorsList=query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询代理商名称失败！");
		}
		
		return operatorsList;
	}

	@Override
	public List<Object> selSnCount( Long opId ) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<Object> returnList=new ArrayList<Object>();
		
		try {
			StringBuffer hql= new StringBuffer();
			
			hql.append( " SELECT P2.AGENT_ID , TMP.REAL_NAME, P2.NUMS ," );
			hql.append( "        (select o1.real_name from T_OPERATORS o1 where tmp.COMPANYID = o1.id) COMPANYNAME, " );
			hql.append( "        (select o2.real_name from T_OPERATORS o2 where tmp.ONEAGENTID = o2.id) ONEAGENTNAME " );
			hql.append( "   FROM (SELECT P1.AGENT_ID AGENT_ID, sum(P1.NUMS) NUMS FROM YJ_POS_BATCH P1 " );
			hql.append( "          GROUP BY P1.AGENT_ID) P2, " );
			hql.append( "        (SELECT TO_CHAR(T.ID) TID, t.REAL_NAME , t.COMPANYID , t.ONEAGENTID " );
			hql.append( "           FROM T_OPERATORS T " );
			hql.append( "          START WITH ID = " + opId + " " );
			hql.append( "         CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
			hql.append( "                 AND T.ROLE_TYPE = '03') TMP " );
			hql.append( "  WHERE TMP.tId = P2.AGENT_ID " );

	    	SQLQuery query=session.createSQLQuery(hql.toString());
	        
			returnList = query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询仓库导入的SN数量失败！");
		}
		
		return returnList;
	}
	

	@Override
	public List<Object> selSelfSupportSnCount( Long opId , String orgCode) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<Object> returnList=new ArrayList<Object>();
		
		try {
			StringBuffer hql= new StringBuffer();
			
			hql.append( " select TS.AGENT_ID, " );
			hql.append( "        TMP.REAL_NAME, " );
			hql.append( "        TS.NUMS, " );
			hql.append( "        (SELECT T4.REAL_NAME FROM T_OPERATORS T4 WHERE T4.ID = TMP.COMPANYID) COMPANYNAME, " );
			hql.append( "        (SELECT T4.REAL_NAME FROM T_OPERATORS T4 WHERE T4.ID = TMP.ONEAGENTID) ONEAGENTNAME " );
			hql.append( "   from ( " );
			hql.append( "         select R1.ID AGENT_ID,  " );
			hql.append( "                COUNT(1) NUMS  " );
			hql.append( "           from ( " );
	//		hql.append( "                 -- 查询代理商导入的所有自营终端 " );
			hql.append( "                  (SELECT TO_CHAR(O1.ID) ID  " );
			hql.append( "                     FROM YJ_POS_INFO T1, T_OPERATORS O1 " );
			hql.append( "                    WHERE T1.OPERATORS = O1.ACCOUNT_NO " );
			hql.append( "                      AND 0 = (select count(1) " );
			hql.append( "                                 from YJ_POS_BATCH_INFO P1 " );
			hql.append( "                                where T1.Serial_Num = P1.Serial_Num " );
			hql.append( "                                  and t1.pos_brand = P1.Pos_Brand)) UNION ALL " );
	//		hql.append( "                 -- 查询所有切机的自营终端【注：终端表内有但是SN导入表没有】 " );
			hql.append( "                  (SELECT MI.EXT1 ID  " );
			hql.append( "                     FROM TBL_TERM_INF TI, TBL_MCHT_INF MI, T_OPERATORS T2 " );
			hql.append( "                    WHERE TI.MCHT_CD = MI.MCHT_CD " );
			hql.append( "                      AND MI.EXT1 = T2.ID " );
			hql.append( "                      AND INSTR(TI.DEVICE_CD, 'YJ') <= 0 " );
			hql.append( "                      AND TI.REC_UPD_TS IS NOT NULL " );
			hql.append( "                          " );
			hql.append( "                      AND TI.BELONG_SUB = '" + orgCode + "' " );
			hql.append( "                      AND 0 = (SELECT COUNT(1) " );
			hql.append( "                                 FROM YJ_POS_BATCH_INFO P1 " );
			hql.append( "                                WHERE TI.DEVICE_CD = P1.SERIAL_NUM " );
			hql.append( "                                  AND TI.TMNL_BRAND = P1.POS_BRAND) " );
			hql.append( "                      AND 0 = (SELECT COUNT(1) " );
			hql.append( "                                 FROM YJ_POS_INFO P2 " );
			hql.append( "                                WHERE TI.DEVICE_CD = P2.SERIAL_NUM " );
			hql.append( "                                  AND TI.TMNL_BRAND = P2.POS_BRAND))) R1 " );
			hql.append( "          GROUP BY R1.ID) TS, " );
			hql.append( "        (SELECT TO_CHAR(T.ID) TID, t.REAL_NAME, t.COMPANYID, t.ONEAGENTID " );
			hql.append( "           FROM T_OPERATORS T " );
			hql.append( "          START WITH ID = " + opId + " " );
			hql.append( "         CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
			hql.append( "                AND T.ROLE_TYPE = '03') TMP " );
			hql.append( "  WHERE TMP.tId = TS.AGENT_ID " );

	    	SQLQuery query=session.createSQLQuery(hql.toString());
	        
			returnList = query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询自营导入的SN数量失败！");
		}
		
		return returnList;
	}

	@Override
	public boolean selOperatorInfoByNumberCode ( String agentType , String agentNumber ) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		boolean flag = false;
		try {
			StringBuffer hql= new StringBuffer();
			hql.append( "  select count(1) " );
			hql.append( "    from t_operators t " );
			hql.append( "   where t.role_type = '02' " );
			hql.append( "     and t.organization_code is not null " );
			hql.append( "     and subStr(t.organization_code, 0, (INSTR(t.organization_code, '-'))) = '" + agentType + "' " );
			hql.append( "     and to_number ( subStr(t.organization_code, " );
			hql.append( "                (INSTR(t.organization_code, '-') + 1), " );
			hql.append( "                length(t.organization_code)) ) = to_number('" + agentNumber + "') " );
			
	    	SQLQuery query=session.createSQLQuery(hql.toString());
	        
	    	BigDecimal count = (BigDecimal)query.uniqueResult();
	    	if ( count.compareTo(new BigDecimal("0")) == 1 ) {
	    		flag = true;
	    	}
	    	
		} catch (HibernateException e) {
			throw new DaoException("查询操作员信息失败！");
		}
		
		return flag;
	}


	@Override
	public String selAgentNumberByAreaId(String areaId) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		String agentNumber = "";
		try {
			StringBuffer hql= new StringBuffer();
			hql.append( "  select t.remark1 from tbl_area t where t.id =  " + areaId );
			
	    	SQLQuery query=session.createSQLQuery(hql.toString());
	        
	    	agentNumber = (String)query.uniqueResult();
	    	
		} catch (HibernateException e) {
			throw new DaoException("查询分公司序号信息失败！");
		}
		
		return agentNumber;
	}

	@Override
	public List<Object> selAreaInfoByOrgCode( String orgCode ) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<Object> returnList=new ArrayList<Object>();
		
		try {
			StringBuffer hql= new StringBuffer();

			hql.append( " select * from tbl_area t where t.status = 1 " );
			
			if ( null != orgCode && !"".equals(orgCode) ) {
				hql.append( " and t.ins_code = " + orgCode );
			}

			SQLQuery query=session.createSQLQuery(hql.toString());
			        
			returnList = query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询某机构下分公司属性信息失败！");
		}
				
		return returnList;
	}

	@Override
	public List<Object> selManagerInfoBySupId( String supId ) throws DaoException {
		Session session=sessionFactory.getCurrentSession();
		List<Object> returnList=new ArrayList<Object>();
		
		try {
			StringBuffer hql= new StringBuffer();

			hql.append( "SELECT * FROM t_operators t where  t.role_type = '02' and t.supri_id = " + supId );
			hql.append( " order by t.companyid,t.oneagentid,t.id " );
			
			SQLQuery query=session.createSQLQuery(hql.toString());
			        
			returnList = query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询所有机构下信息失败！");
		}
				
		return returnList;
	}

	@Override
	public List<Object> queryOperatorsByParams(String roleType, String oneagentId) throws DaoException {
		Session session = sessionFactory.getCurrentSession();
		List<Object> returnList=new ArrayList<Object>();
		try {
			StringBuffer hql= new StringBuffer();
			if(Role.OPERATORS_COMM.getCode().equals(roleType)){
				hql.append("select * from t_operators o where o.group_cd='2' and o.role_type in('02','03') and o.oneagentid="+oneagentId);
			}
			if(Role.OPERATORS_LEAD.getCode().equals(roleType)){
				hql.append("select * from t_operators o where o.group_cd='2' and o.role_type in ('02','03')");
			}
			SQLQuery query=session.createSQLQuery(hql.toString());
			returnList = query.list();
		} catch (HibernateException e) {
			throw new DaoException("查询代理商列表失败！");
		}
		return returnList;
	}
	
}
